package com.ZYHsheep.dao;

import com.ZYHsheep.entity.Castrate;
import com.ZYHsheep.entity.Sheep;
import com.ZYHsheep.util.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SheepDao {
    /*查询所有羊只信息*/
    public List<Sheep> findAll() {
        List<Sheep> list = new ArrayList<>();
        String sql = "SELECT * FROM bas_sheep";
        try (Connection c = JDBCUtil.getConn();
             PreparedStatement ps = c.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Sheep s = new Sheep();
                s.setSno(rs.getInt("Sno"));
                s.setSex(rs.getString("Sex"));
                s.setVariety(rs.getString("Variety"));
                s.setBodyMeasure(rs.getFloat("body_measure"));
                s.setBodyScore(rs.getInt("body_score"));
                s.setStatus(rs.getString("status"));
                list.add(s);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /*新增羊只*/
    public int insert(Sheep sheep) {
        String sql = "INSERT INTO bas_sheep(Sex,Variety,body_measure,body_score) VALUES (?,?,?,?)";
        try (Connection c = JDBCUtil.getConn();
             PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, sheep.getSex());
            ps.setString(2, sheep.getVariety());
            ps.setObject(3, sheep.getBodyMeasure());
            ps.setObject(4, sheep.getBodyScore());
            int rows = ps.executeUpdate();
            if (rows > 0) {
                ResultSet keys = ps.getGeneratedKeys();
                if (keys.next()) sheep.setSno(keys.getInt(1));
            }
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /*修改羊只信息 */
    public int update(Sheep sheep) {
        String sql = "UPDATE bas_sheep SET Sex=?,Variety=?,body_measure=?,body_score=? WHERE Sno=?";
        try (Connection c = JDBCUtil.getConn();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, sheep.getSex());
            ps.setString(2, sheep.getVariety());
            ps.setObject(3, sheep.getBodyMeasure());
            ps.setObject(4, sheep.getBodyScore());
            ps.setInt(5, sheep.getSno());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /* 通过ID查询 */
    public Sheep findById(int sno) {
        String sql = "SELECT * FROM bas_sheep WHERE Sno=?";
        try (Connection c = JDBCUtil.getConn();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, sno);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    Sheep s = new Sheep();
                    s.setSno(rs.getInt("Sno"));
                    s.setSex(rs.getString("Sex"));
                    s.setVariety(rs.getString("Variety"));
                    s.setBodyMeasure(rs.getFloat("body_measure"));
                    s.setBodyScore(rs.getInt("body_score"));
                    s.setStatus(rs.getString("status"));
                    return s;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /*去势*/
    public int castrate(int sno, String technician) {
        Connection conn = null;
        try {
            conn = JDBCUtil.getConn();
            conn.setAutoCommit(false);

            try (PreparedStatement ps = conn.prepareStatement(
                    "UPDATE bas_sheep SET Sex = '阉羊' WHERE Sno = ? AND Sex = '公羊'")) {
                ps.setInt(1, sno);
                if (ps.executeUpdate() == 0) {
                    conn.rollback();
                    return 0;
                }
            }

            CastrateDao cd = new CastrateDao();
            Castrate c = new Castrate(sno, technician);
            if (cd.insert(c, conn) == 0) {
                conn.rollback();
                return 0;
            }

            conn.commit();
            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
            if (conn != null) try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); }
            return 0;
        } finally {
            if (conn != null) try { conn.setAutoCommit(true); conn.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }


    //羊只死亡
    public int setDead(int sno) {
        String sql = "UPDATE bas_sheep SET status='死亡' WHERE Sno=? AND status='存活'";
        try (Connection c = JDBCUtil.getConn();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, sno);
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}
